package cn.edu.pku.sei.ra.guineapig.productartist.ejb;

import java.rmi.RemoteException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import javax.ejb.CreateException;
import javax.ejb.EJBException;
import javax.ejb.SessionBean;
import javax.ejb.SessionContext;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

import cn.edu.pku.sei.ra.guineapig.rss.intf.ItemInfor;

public class ProductArtistEJB implements SessionBean {
	
	Connection conn=null;
	
	public ItemInfor[] getItems() throws RemoteException{
		try {
			Statement stmt=conn.createStatement();
			ResultSet res=stmt.executeQuery(
					"select " +
					"item.productid,item.itemid, " +
					"item_details.unitcost, product_details.name, " +
					"product_details.descn " +
					"from " +
					"item_details,item,product_details " +
					"where " +
					"item.itemid=item_details.itemid and " +
					"item.productid=product_details.productid and " +
					"product_details.locale='en_US' and " +
					"item_details.locale='en_US' " +
					"order by " +
					"product_details.name;");
			ArrayList<ItemInfor> arr=new ArrayList<ItemInfor>();
			String lastpid="";
			ItemInfor ii=null;
			while(res.next()){
				String pid=res.getString(1);
				String iid=res.getString(2);
				String price=res.getString(3);
				String name=res.getString(4);
				String descn=res.getString(5);
				if(pid.equals(lastpid)){
					ii.desc=ii.desc+", "+price;
				}
				else{
					if(ii!=null){
						ii.desc=ii.desc+")";
						arr.add(ii);
					}
					ii=new ItemInfor(
							name, 
							"http://127.0.0.1:9000/petstore/product.screen?product_id="+pid,
							descn+" with prices ("+price
						);
					lastpid=pid;
				}
			}
			return arr.toArray(new ItemInfor[1]);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		}
	}
	
	public String getMainTitle() throws RemoteException{
		return "List information of products with summarized price information";
	}
	
	public void ejbCreate() throws CreateException{
		try {
			InitialContext ic= new InitialContext();
			DataSource ds=(DataSource)ic.lookup("java:comp/env/jdbc/CatalogDB");
			conn=ds.getConnection();
		} catch (NamingException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	public void ejbActivate() throws EJBException, RemoteException {
		// TODO Auto-generated method stub

	}

	public void ejbPassivate() throws EJBException, RemoteException {
		// TODO Auto-generated method stub

	}

	public void ejbRemove() throws EJBException, RemoteException {
		// TODO Auto-generated method stub

	}

	public void setSessionContext(SessionContext arg0) throws EJBException,
			RemoteException {
		// TODO Auto-generated method stub

	}

}
